package com.insight.datasource.adapter;

import com.insight.common.util.EncryptionUtil;
import com.insight.entity.DataSource;
import com.insight.entity.MetadataColumn;
import com.insight.entity.MetadataIndex;
import com.insight.entity.MetadataTable;
import lombok.extern.slf4j.Slf4j;

import java.sql.*;
import java.util.*;

/**
 * T062: PostgreSQL元数据提取器实现
 * 使用PostgreSQL INFORMATION_SCHEMA和pg_catalog系统表提取元数据
 *
 * <p>支持的PostgreSQL版本: 12+, 13+, 14+, 15+, 16+
 *
 * <p>核心查询表:
 * <ul>
 *   <li>pg_catalog.pg_tables - 表元数据</li>
 *   <li>INFORMATION_SCHEMA.COLUMNS - 列元数据</li>
 *   <li>pg_catalog.pg_indexes - 索引元数据</li>
 *   <li>pg_catalog.pg_stat_user_tables - 表统计信息</li>
 * </ul>
 *
 * @author Insight Team
 */
@Slf4j
public class PostgreSQLMetadataExtractor implements MetadataExtractor {

    /**
     * 连接超时时间(秒)
     */
    private static final int CONNECTION_TIMEOUT = 10;

    /**
     * 查询超时时间(秒)
     */
    private static final int QUERY_TIMEOUT = 30;

    @Override
    public List<MetadataTable> extractTables(DataSource dataSource, String schemaFilter) {
        log.info("开始提取PostgreSQL表列表: dataSource={}, schema={}", dataSource.getName(), schemaFilter);

        List<MetadataTable> tables = new ArrayList<>();
        String schema = schemaFilter != null ? schemaFilter : "public";

        String sql = """
                SELECT
                    t.schemaname AS table_schema,
                    t.tablename AS table_name,
                    'TABLE' AS table_type,
                    obj_description((quote_ident(t.schemaname) || '.' || quote_ident(t.tablename))::regclass, 'pg_class') AS table_comment,
                    s.n_live_tup AS row_count,
                    pg_total_relation_size((quote_ident(t.schemaname) || '.' || quote_ident(t.tablename))::regclass) AS total_size
                FROM pg_catalog.pg_tables t
                LEFT JOIN pg_catalog.pg_stat_user_tables s
                    ON t.schemaname = s.schemaname AND t.tablename = s.relname
                WHERE t.schemaname = ?
                ORDER BY t.tablename
                """;

        try (Connection conn = createConnection(dataSource);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setQueryTimeout(QUERY_TIMEOUT);
            pstmt.setString(1, schema);

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    MetadataTable table = new MetadataTable();
                    table.setDataSource(dataSource);
                    table.setTableSchema(rs.getString("table_schema"));
                    table.setTableName(rs.getString("table_name"));
                    table.setTableType(rs.getString("table_type"));
                    table.setTableComment(rs.getString("table_comment"));

                    // PostgreSQL的n_live_tup是近似行数
                    Long rowCount = rs.getLong("row_count");
                    if (!rs.wasNull()) {
                        table.setRowCount(rowCount);
                    }

                    // 总大小(包含数据和索引)
                    Long totalSize = rs.getLong("total_size");
                    if (!rs.wasNull()) {
                        // table.setDataLength removed - field does not exist in PostgreSQL schema
                    }

                    tables.add(table);
                }
            }

            log.info("成功提取PostgreSQL表列表: dataSource={}, tableCount={}", dataSource.getName(), tables.size());
            return tables;

        } catch (SQLException e) {
            log.error("提取PostgreSQL表列表失败: dataSource={}, error={}", dataSource.getName(), e.getMessage(), e);
            throw new RuntimeException("提取PostgreSQL表列表失败: " + e.getMessage(), e);
        }
    }

    @Override
    public List<MetadataColumn> extractColumns(DataSource dataSource, MetadataTable table) {
        log.info("开始提取PostgreSQL列信息: table={}", table.getFullName());

        List<MetadataColumn> columns = new ArrayList<>();

        String sql = """
                SELECT
                    c.column_name,
                    c.ordinal_position,
                    c.column_default,
                    c.is_nullable,
                    c.data_type,
                    c.character_maximum_length,
                    c.numeric_precision,
                    c.numeric_scale,
                    c.udt_name,
                    col_description((quote_ident(c.table_schema) || '.' || quote_ident(c.table_name))::regclass, c.ordinal_position) AS column_comment,
                    CASE WHEN pk.column_name IS NOT NULL THEN true ELSE false END AS is_primary_key
                FROM information_schema.columns c
                LEFT JOIN (
                    SELECT ku.column_name
                    FROM information_schema.table_constraints tc
                    JOIN information_schema.key_column_usage ku
                        ON tc.constraint_name = ku.constraint_name
                        AND tc.table_schema = ku.table_schema
                        AND tc.table_name = ku.table_name
                    WHERE tc.constraint_type = 'PRIMARY KEY'
                        AND tc.table_schema = ?
                        AND tc.table_name = ?
                ) pk ON c.column_name = pk.column_name
                WHERE c.table_schema = ?
                  AND c.table_name = ?
                ORDER BY c.ordinal_position
                """;

        try (Connection conn = createConnection(dataSource);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setQueryTimeout(QUERY_TIMEOUT);
            pstmt.setString(1, table.getTableSchema());
            pstmt.setString(2, table.getTableName());
            pstmt.setString(3, table.getTableSchema());
            pstmt.setString(4, table.getTableName());

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    MetadataColumn column = new MetadataColumn();
                    column.setTable(table);
                    column.setColumnName(rs.getString("column_name"));
                    column.setColumnPosition(rs.getInt("ordinal_position"));

                    // PostgreSQL的column_default可能包含类型转换,需要清理
                    String defaultValue = rs.getString("column_default");
                    if (defaultValue != null) {
                        // 移除类型转换符号: '0'::integer -> 0
                        defaultValue = defaultValue.replaceAll("::.*$", "").replaceAll("^'(.*)'$", "$1");
                    }
                    column.setDefaultValue(defaultValue);

                    column.setNullable("YES".equals(rs.getString("is_nullable")));
                    column.setDataType(rs.getString("data_type"));

                    // 字符类型长度
                    Integer charLength = rs.getInt("character_maximum_length");
                    if (!rs.wasNull()) {
                        column.setColumnLength(charLength);
                    }

                    // 数值类型精度和小数位
                    Integer numericPrecision = rs.getInt("numeric_precision");
                    if (!rs.wasNull()) {
                        column.setColumnPrecision(numericPrecision);
                    }

                    Integer numericScale = rs.getInt("numeric_scale");
                    if (!rs.wasNull()) {
                        column.setColumnScale(numericScale);
                    }

                    // 主键判断
                    column.setIsPrimaryKey(rs.getBoolean("is_primary_key"));

                    // PostgreSQL的序列(自增)判断 - 检查默认值是否为nextval()
                    String colDefault = rs.getString("column_default");

                    column.setColumnComment(rs.getString("column_comment"));

                    columns.add(column);
                }
            }

            log.info("成功提取PostgreSQL列信息: table={}, columnCount={}", table.getFullName(), columns.size());
            return columns;

        } catch (SQLException e) {
            log.error("提取PostgreSQL列信息失败: table={}, error={}", table.getFullName(), e.getMessage(), e);
            throw new RuntimeException("提取PostgreSQL列信息失败: " + e.getMessage(), e);
        }
    }

    @Override
    public List<MetadataIndex> extractIndexes(DataSource dataSource, MetadataTable table) {
        log.info("开始提取PostgreSQL索引信息: table={}", table.getFullName());

        Map<String, MetadataIndex> indexMap = new LinkedHashMap<>();

        String sql = """
                SELECT
                    i.indexname AS index_name,
                    i.indexdef AS index_definition,
                    ix.indisunique AS is_unique,
                    am.amname AS index_type,
                    a.attname AS column_name,
                    a.attnum AS column_position
                FROM pg_catalog.pg_indexes i
                JOIN pg_catalog.pg_class c ON c.relname = i.tablename
                JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace AND n.nspname = i.schemaname
                JOIN pg_catalog.pg_index ix ON ix.indexrelid = (i.schemaname || '.' || i.indexname)::regclass
                JOIN pg_catalog.pg_class ic ON ic.oid = ix.indexrelid
                JOIN pg_catalog.pg_am am ON am.oid = ic.relam
                JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid AND a.attnum = ANY(ix.indkey)
                WHERE i.schemaname = ?
                  AND i.tablename = ?
                ORDER BY i.indexname, a.attnum
                """;

        try (Connection conn = createConnection(dataSource);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setQueryTimeout(QUERY_TIMEOUT);
            pstmt.setString(1, table.getTableSchema());
            pstmt.setString(2, table.getTableName());

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    String indexName = rs.getString("index_name");
                    String columnName = rs.getString("column_name");
                    boolean isUnique = rs.getBoolean("is_unique");
                    String indexType = rs.getString("index_type");

                    // 获取或创建索引对象
                    MetadataIndex index = indexMap.computeIfAbsent(indexName, k -> {
                        MetadataIndex newIndex = new MetadataIndex();
                        newIndex.setTable(table);
                        newIndex.setIndexName(indexName);
                        newIndex.setIsUnique(isUnique);
                        newIndex.setIndexType(indexType);
                        return newIndex;
                    });

                    // 追加列名(复合索引)
                    String existingColumns = index.getColumnNames();
                    if (existingColumns == null || existingColumns.isEmpty()) {
                        index.setColumnNames(columnName);
                    } else {
                        index.setColumnNames(existingColumns + "," + columnName);
                    }
                }
            }

            List<MetadataIndex> indexes = new ArrayList<>(indexMap.values());

            log.info("成功提取PostgreSQL索引信息: table={}, indexCount={}", table.getFullName(), indexes.size());
            return indexes;

        } catch (SQLException e) {
            log.error("提取PostgreSQL索引信息失败: table={}, error={}", table.getFullName(), e.getMessage(), e);
            throw new RuntimeException("提取PostgreSQL索引信息失败: " + e.getMessage(), e);
        }
    }

    @Override
    public boolean testConnection(DataSource dataSource) {
        log.info("测试PostgreSQL连接: dataSource={}", dataSource.getName());

        String sql = "SELECT 1";

        try (Connection conn = createConnection(dataSource);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setQueryTimeout(CONNECTION_TIMEOUT);

            try (ResultSet rs = pstmt.executeQuery()) {
                boolean result = rs.next() && rs.getInt(1) == 1;
                log.info("PostgreSQL连接测试{}: dataSource={}",
                        result ? "成功" : "失败", dataSource.getName());
                return result;
            }

        } catch (SQLException e) {
            log.error("PostgreSQL连接测试失败: dataSource={}, error={}",
                    dataSource.getName(), e.getMessage());
            return false;
        }
    }

    @Override
    public String getDatabaseVersion(DataSource dataSource) {
        log.info("获取PostgreSQL版本信息: dataSource={}", dataSource.getName());

        String sql = "SELECT version()";

        try (Connection conn = createConnection(dataSource);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setQueryTimeout(QUERY_TIMEOUT);

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    String version = rs.getString(1);
                    log.info("PostgreSQL版本: dataSource={}, version={}", dataSource.getName(), version);
                    return version;
                }
            }

        } catch (SQLException e) {
            log.error("获取PostgreSQL版本失败: dataSource={}, error={}",
                    dataSource.getName(), e.getMessage());
        }

        return "未知版本";
    }

    @Override
    public String getCurrentSchema(DataSource dataSource) {
        log.info("获取PostgreSQL当前schema: dataSource={}", dataSource.getName());

        String sql = "SELECT current_schema()";

        try (Connection conn = createConnection(dataSource);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setQueryTimeout(QUERY_TIMEOUT);

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    String schema = rs.getString(1);
                    log.info("PostgreSQL当前schema: dataSource={}, schema={}", dataSource.getName(), schema);
                    return schema;
                }
            }

        } catch (SQLException e) {
            log.error("获取PostgreSQL当前schema失败: dataSource={}, error={}",
                    dataSource.getName(), e.getMessage());
        }

        return null;
    }

    /**
     * 创建PostgreSQL数据库连接
     *
     * @param dataSource 数据源配置
     * @return JDBC连接
     * @throws SQLException 连接异常
     */
    private Connection createConnection(DataSource dataSource) throws SQLException {
        String jdbcUrl = buildJdbcUrl(dataSource);

        Properties props = new Properties();
        props.setProperty("user", dataSource.getUsername() != null ? dataSource.getUsername() : "");
        // 解密密码 - 使用passwordEncrypted字段
        String decryptedPassword = "";
        if (dataSource.getPasswordEncrypted() != null && !dataSource.getPasswordEncrypted().isEmpty()) {
            try {
                decryptedPassword = EncryptionUtil.decrypt(dataSource.getPasswordEncrypted());
            } catch (Exception e) {
                log.error("解密密码失败: dataSource={}, error={}", dataSource.getName(), e.getMessage());
                throw new SQLException("密码解密失败: " + e.getMessage(), e);
            }
        }
        props.setProperty("password", decryptedPassword);
        props.setProperty("ApplicationName", "Insight-Metadata-Extractor");
        props.setProperty("connectTimeout", String.valueOf(CONNECTION_TIMEOUT));

        log.debug("创建PostgreSQL连接: url={}", jdbcUrl);

        DriverManager.setLoginTimeout(CONNECTION_TIMEOUT);
        return DriverManager.getConnection(jdbcUrl, props);
    }

    /**
     * 构建PostgreSQL JDBC URL
     *
     * @param dataSource 数据源配置
     * @return JDBC URL
     */
    private String buildJdbcUrl(DataSource dataSource) {
        return String.format("jdbc:postgresql://%s:%d/%s",
                dataSource.getHost(),
                dataSource.getPort(),
                dataSource.getDatabaseName());
    }
}
